﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using QuanLyHocPhi.Entity;

namespace QuanLyHocPhi.DAL
{
    public class TY_GIA_DAO
    {
        private const string SQL_SELECT_RATES = "select * from TY_GIA order by NGAY_TY_GIA DESC";
        private const string SQL_SELECT_RATE_BYID = "select * from TY_GIA where MA_TY_GIA = @MA_TY_GIA";
        private const string SQL_SELECT_RATE_AT_DATE = "select top (1) * from TY_GIA where DATEPART(yyyy,NGAY_TY_GIA) = @Year and DATEPART(mm,NGAY_TY_GIA) = @Month and DATEPART(dd,NGAY_TY_GIA) = @Day order by NGAY_TY_GIA";
        private const string SQL_SELECT_RECENT_RATE = "select top (1) * from TY_GIA order by NGAY_TY_GIA DESC";
        private const string SQL_SELECT_RATE_AT_DATE_DIFFERENT_ID = "select top (1) * from TY_GIA where DATEPART(yyyy,NGAY_TY_GIA) = @Year and DATEPART(mm,NGAY_TY_GIA) = @Month and DATEPART(dd,NGAY_TY_GIA) = @Day and MA_TY_GIA <> @MA_TY_GIA order by NGAY_TY_GIA";
        private const string SQL_SELECT_IDENTITY_PARM = "select @@IDENTITY";
        private const string SQL_INSERT_RATE = "insert into TY_GIA (TY_GIA_USD, TY_GIA_VND, NGAY_TY_GIA) values(@TY_GIA_USD, @TY_GIA_VND, @NGAY_TY_GIA)";

        private const string SQL_UPDATE_RATE = "update TY_GIA set TY_GIA_USD = @TY_GIA_USD, TY_GIA_VND = @TY_GIA_VND, NGAY_TY_GIA = @NGAY_TY_GIA where MA_TY_GIA = @MA_TY_GIA";

        private const string SQL_DELETE_RATE = "delete from TY_GIA where MA_TY_GIA = @MA_TY_GIA";

        // Parameters
        private const string PARM_RATE_ID = "@MA_TY_GIA";
        private const string PARM_RATE_USD = "@TY_GIA_USD";
        private const string PARM_RATE_VND = "@TY_GIA_VND";
        private const string PARM_RATE_Date = "@NGAY_TY_GIA";
        private const string PARM_RATE_YEAR = "@Year";
        private const string PARM_RATE_MONTH = "@Month";
        private const string PARM_RATE_DAY = "@Day";

        

        #region Public Methods

        public IList<TY_GIA> GetAll()
        {
            IList<TY_GIA> rates = new List<TY_GIA>();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_SELECT_RATES, null))
            {
                while (reader.Read())
                {
                    TY_GIA rate = new TY_GIA();
                    rate.MA_TY_GIA = reader.GetInt32(reader.GetOrdinal("MA_TY_GIA"));
                    rate.TY_GIA_USD =(decimal) reader.GetDouble(reader.GetOrdinal("TY_GIA_USD"));
                    rate.TY_GIA_VND = (decimal)reader.GetDouble(reader.GetOrdinal("TY_GIA_VND"));
                    rate.NGAY_TY_GIA = reader.GetDateTime(reader.GetOrdinal("NGAY_TY_GIA"));
                    rates.Add(rate);
                }
            }
            return rates;
        }

        public TY_GIA GetRate(int id)
        {
            TY_GIA tygia = null;
            SqlParameter[] parms;
            parms = new SqlParameter[] {
											 new SqlParameter(PARM_RATE_ID, SqlDbType.Int)};
            parms[0].Value = id;
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_SELECT_RATE_BYID, parms))
            {
                if (reader.Read())
                {
                    tygia = new TY_GIA();
                    tygia.MA_TY_GIA = reader.GetInt32(reader.GetOrdinal("MA_TY_GIA"));
                    tygia.TY_GIA_USD = reader.GetDecimal(reader.GetOrdinal("TY_GIA_USD"));
                    tygia.TY_GIA_VND = reader.GetDecimal(reader.GetOrdinal("TY_GIA_VND"));
                    tygia.NGAY_TY_GIA = reader.GetDateTime(reader.GetOrdinal("NGAY_TY_GIA"));
                }
            }
            return tygia;
        }
        public TY_GIA GetRecentRate()
        {
            TY_GIA tygia = null;           
       
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_SELECT_RECENT_RATE, null))
            {
                if (reader.Read())
                {
                    tygia = new TY_GIA();
                    tygia.MA_TY_GIA = reader.GetInt32(reader.GetOrdinal("MA_TY_GIA"));
                    tygia.TY_GIA_USD = (decimal)reader.GetDouble(reader.GetOrdinal("TY_GIA_USD"));
                    tygia.TY_GIA_VND = (decimal)reader.GetDouble(reader.GetOrdinal("TY_GIA_VND"));
                    tygia.NGAY_TY_GIA = reader.GetDateTime(reader.GetOrdinal("NGAY_TY_GIA"));
                }
            }
            return tygia;
        }
        public TY_GIA GetRateAtDate(DateTime date)
        {
            TY_GIA tygia = null;
            SqlParameter[] parms;
            parms = new SqlParameter[] {
											 new SqlParameter(PARM_RATE_YEAR, SqlDbType.Int),
											 new SqlParameter(PARM_RATE_MONTH, SqlDbType.Int),
                                             new SqlParameter(PARM_RATE_DAY, SqlDbType.Int)};
            parms[0].Value = date.Year;
            parms[1].Value = date.Month;
            parms[2].Value = date.Day;
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_SELECT_RATE_AT_DATE, parms))
            {
                if (reader.Read())
                {
                    tygia = new TY_GIA();
                    tygia.MA_TY_GIA = reader.GetInt32(reader.GetOrdinal("MA_TY_GIA"));
                    tygia.TY_GIA_USD =(decimal) reader.GetDouble(reader.GetOrdinal("TY_GIA_USD"));
                    tygia.TY_GIA_VND =(decimal) reader.GetDouble(reader.GetOrdinal("TY_GIA_VND"));
                    tygia.NGAY_TY_GIA = reader.GetDateTime(reader.GetOrdinal("NGAY_TY_GIA"));
                }
            }
            return tygia;
        }

        public TY_GIA GetRateAtDateDiffId(DateTime date, int MA_TY_GIA)
        {
            TY_GIA rate = null;
            SqlParameter[] parms;
            parms = new SqlParameter[] {
											 new SqlParameter(PARM_RATE_YEAR, SqlDbType.Int),
											 new SqlParameter(PARM_RATE_MONTH, SqlDbType.Int),
                                             new SqlParameter(PARM_RATE_DAY, SqlDbType.Int),
                                             new SqlParameter(PARM_RATE_ID, SqlDbType.Int)};
            parms[0].Value = date.Year;
            parms[1].Value = date.Month;
            parms[2].Value = date.Day;
            parms[3].Value = MA_TY_GIA;
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_SELECT_RATE_AT_DATE_DIFFERENT_ID, parms))
            {
                if (reader.Read())
                {
                    rate = new TY_GIA();
                    rate.MA_TY_GIA = reader.GetInt32(reader.GetOrdinal("MA_TY_GIA"));
                    rate.TY_GIA_USD = reader.GetDecimal(reader.GetOrdinal("TY_GIA_USD"));
                    rate.TY_GIA_VND = reader.GetDecimal(reader.GetOrdinal("TY_GIA_VND"));
                    rate.NGAY_TY_GIA = reader.GetDateTime(reader.GetOrdinal("NGAY_TY_GIA"));
                }
            }
            return rate;
        }

        public bool Insert(TY_GIA rate)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] {
											 new SqlParameter(PARM_RATE_USD, SqlDbType.Float),
											 new SqlParameter(PARM_RATE_VND, SqlDbType.Float),
                                             new SqlParameter(PARM_RATE_Date, SqlDbType.DateTime)};
            parms[0].Value = rate.TY_GIA_USD;
            parms[1].Value = rate.TY_GIA_VND;
            parms[2].Value = rate.NGAY_TY_GIA;

            try
            {
                int result = 0;
                SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
                result = SQLDBHelper.ExecuteNonQuery(conn,
                    CommandType.Text, SQL_INSERT_RATE, parms);
                // get the CategoryID, CategoryID is an Auto number column
                object obj = SQLDBHelper.ExecuteScalar(conn,
                    CommandType.Text, SQL_SELECT_IDENTITY_PARM, null);
                if (obj != null)
                    rate.MA_TY_GIA = int.Parse(obj.ToString());
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
            //return false;
        }

        public bool Update(TY_GIA tygia)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] {
											 new SqlParameter(PARM_RATE_USD, SqlDbType.Float),
											 new SqlParameter(PARM_RATE_VND, SqlDbType.Float),
                                             new SqlParameter(PARM_RATE_Date, SqlDbType.DateTime),
                                             new SqlParameter(PARM_RATE_ID, SqlDbType.Int)};
            parms[0].Value = tygia.TY_GIA_USD;
            parms[1].Value = tygia.TY_GIA_VND;
            parms[2].Value = tygia.NGAY_TY_GIA;
            parms[3].Value = tygia.MA_TY_GIA;

            try
            {
                int result = 0;
                result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING,
                    CommandType.Text, SQL_UPDATE_RATE, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
            //return false;
        }

        public bool Delete(int MA_TY_GIA)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] { new SqlParameter(PARM_RATE_ID, SqlDbType.Int) };
            parms[0].Value = MA_TY_GIA;

            try
            {
                int result = 0;
                result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING,
                    CommandType.Text, SQL_DELETE_RATE, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
            //return false;
        }

        #endregion
    }
}
